import json
import pymysql


class Taobao_spider(object):

    def parse_html(self):
        """
        获取本地文件
        :return: comments内容
        """
        with open('tb_comments_1.json', encoding='utf-8') as f:
            comments = json.load(f)
            # print(comments)
        # with open('tb_comments_2.json', encoding='utf-8') as f:
        #     comments = json.load(f)
        #     # print(comments)
            self.extract(comments)


    def extract(self,comments):
        """
        提取内容
        :param comments:
        :return:
        """
        # 取得列表
        list_content = comments['rateDetail']['rateList']

        data_list = []
        for data in list_content:
            # 评论
            ratecontent = data['rateContent']
            # print(ratecontent)
            # 判断是否有追评
            if not data['appendComment']:
                content = ('此用户没有写追评！！！！')
            else:
                content = data['appendComment']['content']  # 追评
            # print(content)
            # 评论时间
            c_time = data['rateDate']
            # print(c_time)
            # 用户id
            id = data['id']
            # print(id)
            # 手机类型
            leixing = data['auctionSku']
            # print(leixing)
            data_list.append({
                'id': id,
                'c_time': c_time,
                'ratecontent': ratecontent,
                'content': content,
                'leixing': leixing,
            })
        for list in data_list:
            print(list)
            self.sql_con(list)


    def sql_con(self,list):
        db = pymysql.connect(host='127.0.0.1',
                             port=3306,
                             user='root',
                             password='123456',
                             db='taobao',
                             charset='utf8mb4')

        with db.cursor() as cursor:
            sql1 = "select id from taobao.table_name where id=%s;" % (list['id'])
            cursor.execute(sql1)
            rs_set = cursor.fetchone()
            if rs_set:
                print('这条评论已存在在数据库中')
            else:
                sql_v = """insert into taobao.table_name(id,c_time,ratecontent,content,leixing) values (%(id)s, %(c_time)s,%(ratecontent)s,%(content)s,%(leixing)s);"""
                cursor.execute(sql_v, args=list)
            db.commit()
            # 关闭数据库连接
            db.close()

    def run(self):
        """
        执行函数
        :return:
        """
        self.parse_html()


if __name__ == '__main__':
    tobo = Taobao_spider()
    tobo.run()